import java.io.BufferedReader;
import java.io.Console;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.jcraft.jsch.JSchException;


public class EmployeeApp {

	DatabaseHelper db;
	
	Connection cn;

	// currentResults holds current results from a search() so that other methods can access them
	ResultSet currentResults;

	// currentItem holds the row number of an itme the user is looking at (so we can use currentResults.absolute(currentItem) to go to it
	public Integer currentItem = 0;
	
	public EmployeeApp(String dbname, String userid, String password) throws SQLException {
		cn = null;
		currentResults = null;
		currentItem = 0;
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			cn = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+dbname, userid, password);
		} catch (Exception e) {
			System.out.println("connection failed: " + e);
		}
		
		db = new DatabaseHelper(cn, currentResults, currentItem.toString());
		
		// read in the data from the local .dat files
	}

	public static void main (String[] args) throws JSchException, IOException, InterruptedException, SQLException {
		
		//get console to read from
		Console console;
		console = System.console();
		/**
		 * I set this up myself, when I installed my personal Mysql Server 
		 */
		String dbname = "db";
		String userid = "root";
		String password = "toor";

		
		EmployeeApp app = new EmployeeApp(dbname, userid, password);
		
		
		
		
		//app.db.dropAllTables();
		//app.db.init();
		/**
\\

		app.db.executeUpdate("load data infile 'Customers.dat' into table Customers");
		app.db.executeUpdate("load data infile 'products.dat' into table Books");
		app.db.executeUpdate("load data infile 'reviews.dat' into table Reviews");
**/
		// for input from stdin
		String line = "";
		InputStreamReader instream = new InputStreamReader(System.in);
		BufferedReader in = new BufferedReader(instream);
		
		
		System.out.println("init to create database tables: (* RUN ONCE *)");
		System.out.println("search [author|title|...] x to search.");
		System.out.println("select n to select one of the search results.");
		System.out.println("review r text to review your selection.");
		System.out.println("quit to quit.");
		System.out.println("*******Now Running Employee Database App*********");
		System.out.println("searchorders [address | item] <value>  :   searches orders by address or item, where item/address equals <value>");
		System.out.println("fillorder <row> : fill the order in the given row of your searchOrder results");
		System.out.println("viewreviews [asin | custid | review | entered] <value> :  view reviews where category = value (ie viewreviews asin 000200343X");
		System.out.println("deletereview <item_asin> <cust_id> :  delete the review for item 'item_asid' from 'cust_id'");
		// loop while waiting for input
		while (!(line.equals("quit"))) {
			System.out.print("> ");

			try {
				line = in.readLine();
			} catch (Exception e) {
				System.out.println("error: " + e);
			}
			
			// tokenize
			String[] tokens = line.toLowerCase().split("\\s");

			
			// first token is command
			if (tokens[0].equals("search"))
				app.search(tokens);

			else if (tokens[0].equals("select"))
				app.selectOrder(Integer.parseInt(tokens[1]));
			else if (tokens[0].equals("review"))
				;//app.review(tokens);
			else if (tokens[0].equals("quit"))
				;	
			else if (tokens[0].equals("init"))
			    app.db.init();
			else if(tokens[0].equals("vieworders")){
				app.viewOrders();
			}
			else if(tokens[0].equals("deletereview")){
				app.deleteReview(tokens[1], tokens[2]);
			}
			else if(tokens[0].equals("viewreviews")){
				app.viewReviews(tokens);
			}
			else if(tokens[0].equals("searchorders")){
				app.searchOrders(tokens);
			}
			else if(tokens[0].equals("fillorder")){
				if(app.currentItem != null){
					//System.out.println("testingasdl;kfja");
					app.fillOrder(app.currentItem);
				}
				else System.out.println("NO ORDER SELECTED!");
			}
			else
				System.out.println("don't understand request.");
		}

	}
	
	public void selectOrder(int row) throws SQLException{
		
		System.out.println("about to select order at row " + row);
		this.currentResults = this.db.rs;
		int i = 1;
		if(currentResults.next()){
			System.out.println(currentResults.getRow());
			if(i == row){
				this.currentItem = currentResults.getRow();
				System.out.println("found at row " + currentItem);
				
			}
			i++;
		}
		else{
			System.out.println("nothing in the result set");
		}
		while(currentResults.next()){
			System.out.println(currentResults.getRow());
			if(i == row){
				this.currentItem = currentResults.getRow();
				System.out.println("found at row " + currentItem);
				
			}
			i++;
		}
		
		
	}
	
	
	public void searchOrders(String[] tokens) throws SQLException {
		// TODO Auto-generated method stub
		String searchby = tokens[1];
		String name = getFullName(tokens);
		
		if(searchby.equals("item")){
			searchOrdersByName(name);
		}
		else if(searchby.equals("address")){
			searchOrdersByAddress(name);
		}
		else{
			System.out.println("Must enter a parameter to search by:" +
					" (address, item)");
		}
		
	}

	public void searchOrdersByAddress(String address) throws SQLException {
		this.db.rs = this.db.executeQuery("SELECT * FROM ORDERS WHERE address = '" + address + "'");
		this.db.printResultSetOrders(this.db.rs);
		
	}

	// search by title or author (or other things)
	void search(String[] tokens) throws SQLException {
		
		String full_name = new String();
		for(int i = 2; i < tokens.length; i++){
			full_name = full_name + " " + tokens[i];
		}
		full_name = full_name.substring(1);
		// check tokens[1] for search type
		if (tokens[1].equals("title")) {
			// plug title into a prepared SQL query
			this.db.searchBooksBy("title", full_name);
		}
		else if (tokens[1].equals("author")) {
			// plug author into a prepared SQL query
			this.db.searchBooksBy("author", full_name);
		}
		// etc
		else {
			System.out.println("don't understand search request.");
			return;
		}

		// execute query
		// currentResults = ...

		// display results
	}
	
	

	
	void viewOrders() throws SQLException{
		
		this.db.rs = this.db.executeQuery("SELECT * FROM Orders;");
		this.db.printResultSetOrders(this.db.rs);
		
	}
	
	void viewReviews(String[] tokens) throws SQLException{
		String category = tokens[1];
		String item = getFullName(tokens);
		
		this.db.rs = this.db.searchReviewsBy(category, item);

		//this.db.printResultSetReviews(this.db.rs);
		
		
	}
	
	void deleteReview(String asin, String custid) throws SQLException{

		this.db.query = this.db.con.prepareStatement("DELETE FROM Orders WHERE asin = ? AND custid = ?");
		this.db.query.setString(1, asin);
		this.db.query.setString(2, custid);
		this.db.query.executeUpdate();
		
	}
	
	void searchOrdersByName(String name) throws SQLException{
		this.db.rs = this.db.executeQuery("SELECT * FROM ORDERS WHERE item = '" + name + "'");
		this.db.printResultSetOrders(this.db.rs);
	}
	
	void fillOrder(String item, String address) throws SQLException{
		//find item, delete it
		System.out.println(item + " " + address);
		this.db.query = this.db.con.prepareStatement("DELETE FROM Orders WHERE item = ? AND address = ?");
		this.db.query.setString(1, item);
		this.db.query.setString(2, address);
		this.db.query.executeUpdate();
		
		
	}
	
	void fillOrder(int currentItem) throws SQLException{
		
		//System.out.println(currentItem + "WHAST THE FUCK");
		
		int i = 0;
		this.db.rs = db.executeQuery("SELECT * FROM Orders");
		
		while(i < currentItem && this.db.rs.next()){
			i++;
			
		}
		if(this.db.rs.next()){
			String name = this.db.rs.getString("item");
			String address = this.db.rs.getString("address");
			
			fillOrder(name, address);
		}

		
	}
	
	
	
	
	/**
	 * concatenates the remaining tokens[] parameters to form the full title or author's name.
	 * @param tokens 
	 * @return a string containing the full title or author name
	 */
	String getFullName(String[] tokens){
		
		String full_name = new String();
		for(int i = 2; i < tokens.length; i++){
			full_name = full_name + " " + tokens[i];
		}
		
		if(full_name.length() > 1) full_name = full_name.substring(1);
		return full_name;
		
	}
	
	String getFullName1(String[] tokens){
		
		String full_name = new String();
		for(int i = 2; i < tokens.length; i++){
			full_name = full_name + " " + tokens[i];
		}
		
		if(full_name.length() > 1) full_name = full_name.substring(1);
		return full_name;
		
	}
	
}
